<?php
class Controller_Amazonsales extends Controller
{
    public function action_index()
    {
        if( PHP_SAPI !== 'cli' ) return;

        set_time_limit(0);                                                                                              //防超时
        ignore_user_abort(true);
        $count = 0;                                                                                                     //导入计数
        $sec = 8 * 3600;
        $page = 1;                                                                                                      //当前分页
        $maxDate = date('Y-m-d', $_SERVER['REQUEST_TIME']);
        $nowDate = date('Y-m-d H:i:s', $_SERVER['REQUEST_TIME']);
        echo "数据获取开始 : {$nowDate} \n";

        $sql = 'SELECT
            DATE_FORMAT(`add_time`, "%Y-%m-%d") maxDate
        FROM
            `amz_sales`
        ORDER BY
            `id` DESC 
        LIMIT 1';
        $minDate = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();                                    //读取数据库最大时间
        $minDate = empty($minDate) ? date('Y-m-d', $_SERVER['REQUEST_TIME'] - 2592000) : $minDate[0]['maxDate'];        //生成最小范围的过滤条件

        //获取复合条件的id******gqc修改*******
        $sql = "SELECT
            `id`
        FROM
            `orders`
        WHERE
            DATE_FORMAT(`PurchaseDate` + INTERVAL $sec SECOND, '%Y-%m-%d') >= '{$minDate}'
        AND DATE_FORMAT(`PurchaseDate` + INTERVAL $sec SECOND, '%Y-%m-%d') < '{$maxDate}'
        AND OrderStatus NOT IN ('Pending','Canceled')
        AND `isNewErp` = 0";

        stagerpc::getAmazonSkuSales($sql, $ids);
        if(empty($ids))return;
        $ids_str = '';

        foreach($ids as $id){ $ids_str .= empty($ids_str)?$id['id']:','.$id['id'];}

        $sql = "SELECT
            `products`.SellerSKU_Clean sku,
            SUM(`products`.QuantityOrdered) c,
            DATE_FORMAT(`PurchaseDate` + INTERVAL $sec SECOND, '%Y-%m-%d') `date`
        FROM
            `orders`
                LEFT JOIN `products` ON
                    `products`.AmazonOrderId = `orders`.AmazonOrderId
        WHERE
            `orders`.`id` IN ({$ids_str})
        AND `products`.SellerSKU_Clean NOT LIKE '%AUS_%'
        GROUP BY
            `products`.SellerSKU_Clean, DATE_FORMAT(`PurchaseDate` + INTERVAL $sec SECOND, '%Y-%m-%d')";
        while( stagerpc::getAmazonSkuSales($sql . ' LIMIT ' . ($page++ - 1) * 500 . ', 500', $data) ) {
            foreach($data as &$v) {
                $v = '("' . join('","', tool::slashesDeep($v)) . "\", \"{$nowDate}\")";
                $count += 1;
            }
            $data = 'INSERT INTO `amz_sales` (
                `sku`, `sale`, `count_date`, `add_time`
            ) VALUES ' . join(',', $data);
            DB::query(Database::INSERT, $data)->execute('newerp');
        }

        //修改状态
        $sql = "UPDATE `orders` SET `isNewErp` = 1 WHERE `id` IN ($ids_str) OR `OrderStatus` = 'Canceled'";
        stagerpc::getAmazonSkuSales($sql,$ok);

        if((strtotime($maxDate)-strtotime($minDate)) == 86400)$this->toUpdate($minDate);

        checkstock::bomb('amz', $minDate);
        checkstock::badBegin('amz');
        checkstock::activeOut(3, $minDate);
        $nowDate = time() - $_SERVER['REQUEST_TIME'];
        echo "数据插入更新结束 : 导入 {$count} 条, 耗时 {$nowDate} 秒\n";
    }

    public function toUpdate($min) {
        $page = 1;

        $sql = "SELECT
            `id`
        FROM
            `orders`
        WHERE
            DATE_FORMAT(`PurchaseDate` + INTERVAL 28800 SECOND, '%Y-%m-%d') < '{$min}'
        AND DATE_FORMAT(`PurchaseDate` + INTERVAL 28800 SECOND, '%Y-%m-%d') >= '".date('Y-m-d', strtotime("-30 day", strtotime($min)))."'
        AND OrderStatus NOT IN ('Pending','Canceled')
        AND `isNewErp` = 0";

        stagerpc::getAmazonSkuSales($sql, $ids);

        if(empty($ids))return;
        $ids_str = '';
        foreach($ids as $id) {$ids_str .= empty($ids_str)?$id['id']:','.$id['id'];}

        $sql = "SELECT
            `products`.SellerSKU_Clean sku,
            SUM(`products`.QuantityOrdered) c,
            DATE_FORMAT(`PurchaseDate` + INTERVAL 28800 SECOND, '%Y-%m-%d') `date`
        FROM
            `orders`
                LEFT JOIN `products` ON
                    `products`.AmazonOrderId = `orders`.AmazonOrderId
        WHERE
            `orders`.`id` IN ({$ids_str})
        AND `products`.SellerSKU_Clean NOT LIKE '%AUS_%'
        GROUP BY
            `products`.SellerSKU_Clean, DATE_FORMAT(`PurchaseDate` + INTERVAL 28800 SECOND, '%Y-%m-%d')";

        while( stagerpc::getAmazonSkuSales($sql . ' LIMIT ' . ($page++ - 1) * 500 . ', 500', $data) ) {
            if(empty($data))continue;

            foreach($data as $v) {
                //$v = tool::slashesDeep($v);
                $sql = "UPDATE `amz_sales` SET `sale` = `sale` + '{$v['c']}', `up_time` = NOW() WHERE `sku` = '{$v['sku']}' AND `count_date` = '{$v['date']}'";
                DB::query(Database::UPDATE, $sql)->execute('newerp');
            }
        }

        //修改状态
        $sql = "UPDATE `orders` SET `isNewErp`=1 WHERE `id` IN $ids_str";
        stagerpc::getAmazonSkuSales($sql,$ok);
    }
}